package com.is.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.NumberFormat;
import java.util.List;
import com.is.dbconn.DBConnect;
import com.is.model.CourseScore;
import com.is.model.ParamCourseScoreGS;
public class CourseScoreControl {
	
	public static boolean insertCourseScore(CourseScore cs){
		DBConnect dbc = new DBConnect();
		String sql = "insert into course_score(stuId, courseClassId, attendanceScore,dailyScore, midtermScore, finalScore,totalScore, stuyear) values (?,?, ?,?, ?, ?, ?, ?)";
		dbc.prepareStatement(sql);
		try{
			int i = 1;
			dbc.setInt(i++, cs.getStuId());
			dbc.setInt(i++, cs.getCourseClassId());
			dbc.setString(i++, cs.getAttendanceScore());
			dbc.setString(i++, cs.getDailyScore());
			dbc.setString(i++, cs.getMidtermScore());
			dbc.setString(i++, cs.getFinalScore());
			dbc.setString(i++, cs.getTotalScore());
			dbc.setInt(i++, cs.getStuyear());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("调用insertCourseScore函数失败");
			return false;
		} finally{
			dbc.close();
		}
	}
	
	
	public static boolean isInsertCourseScoreByStuyearAndCourseId(int courseClassId,int stuyear){
		DBConnect dbConnect = new DBConnect();
		String sql = "select id from course_score where stuyear='"+stuyear+"' and courseClassId='"+courseClassId+"' limit 1";
		dbConnect.prepareStatement(sql);
		ResultSet rSet = null;
		try {
			rSet = dbConnect.executeQuery();
			if(rSet.next()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("isInserCourseScoreByStuyearAndCourseId ERROR");
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	/**事务提交学生成绩
	 * @param css
	 * @return
	 */
	public static boolean insertCourseScore(List<CourseScore> css) {
		DBConnect dbConnect = new DBConnect();
		String sql = "insert into course_score(stuId, courseClassId, attendanceScore,dailyScore, midtermScore, finalScore,totalScore,stuyear) values (?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = null;
		dbConnect.setAutoCommit(false);
		dbConnect.prepareStatement(sql);
		pstmt = dbConnect.getPreparedStatement();
		try {
			for(CourseScore cs:css){
				int index=1;
				pstmt.setInt(index++, cs.getStuId());
				pstmt.setInt(index++, cs.getCourseClassId());
				pstmt.setString(index++, cs.getAttendanceScore());
				pstmt.setString(index++, cs.getDailyScore());
				pstmt.setString(index++, cs.getMidtermScore());
				pstmt.setString(index++, cs.getFinalScore());
				pstmt.setString(index++, cs.getTotalScore());
				pstmt.setInt(index++, cs.getStuyear());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			dbConnect.rollback();
			e.printStackTrace();
			System.out.println("insertCourseScore(List<CourseScore> css)ERROR");
			return false;
		}finally{
		    dbConnect.close();   
		}
		
	}
	

	/**事务更新学生成绩
	 * @param css
	 * @return
	 */
	public static boolean updateCourseScore(List<CourseScore> css) {
		DBConnect dbConnect = new DBConnect();
		String sql = " update course_score set attendanceScore=?,dailyScore=?, midtermScore=?, finalScore=?,totalScore=?  where stuId= ? and courseClassId= ? and stuyear=?";
		PreparedStatement pstmt = null;
		dbConnect.setAutoCommit(false);
		dbConnect.prepareStatement(sql);
		pstmt = dbConnect.getPreparedStatement();
		try {
			for(CourseScore cs:css){
				int index=1;
				pstmt.setString(index++, cs.getAttendanceScore());
				pstmt.setString(index++, cs.getDailyScore());
				pstmt.setString(index++, cs.getMidtermScore());
				pstmt.setString(index++, cs.getFinalScore());
				pstmt.setString(index++, cs.getTotalScore());
				pstmt.setInt(index++, cs.getStuId());
				pstmt.setInt(index++, cs.getCourseClassId());
				pstmt.setInt(index++, cs.getStuyear());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			dbConnect.rollback();
			e.printStackTrace();
			System.out.println("insertCourseScore(List<CourseScore> css)ERROR");
			return false;
		}finally{
		    dbConnect.close();   
		}
		
	}
	
	

	public static CourseScore getCSByStuIdAndCourseId(int courseClassId,int stuId){
		DBConnect dbc = new DBConnect();
		String sql = "select * from course_score where stuId ='"+stuId+"' and courseClassId = '"+courseClassId+"'";
		dbc.prepareStatement(sql);
		//System.out.println(sql);
		CourseScore cs = null;
		ResultSet rs = null;
		try{
			rs = dbc.executeQuery();
			if(rs.next()){
				cs = new CourseScore();
				cs.setId(rs.getInt("id"));
				cs.setStuId(rs.getInt("stuId"));
				cs.setCourseClassId(rs.getInt("courseClassId"));
				cs.setAttendanceScore(rs.getString("attendanceScore"));
				cs.setDailyScore(rs.getString("dailyScore"));
				cs.setMidtermScore(rs.getString("midtermScore"));
				cs.setFinalScore(rs.getString("finalScore"));
				cs.setTotalScore(rs.getString("totalScore"));
				cs.setStuyear(rs.getInt("stuyear"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("调用getCSByStuIdAndCourseId函数失败");
		} finally{
			dbc.close();
		}
		return cs;
	}
	
	/***
	 * 计算某同学在某课程的出勤分数
	 */
	 public static String getAttendanceScoreByCCIDAndStuId(int ccid,int stuId,int stuyear) {
		 int countTimes = AttendanceTimeControl.getCount(ccid,stuyear,"","");
		 float totalQQ=0;
		//  int totalDM = AttendanceTimeControl.getCount(ccid,stuyear,"","");
		  boolean flag = true;
         int count2 = AttendanceDetailControl.getCount(2,stuId,ccid+"",stuyear,"","");
         int num2 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(2,stuyear);
         if(num2==0){
       	  flag = false;
         }else{
       	  totalQQ+= (float)count2/num2;
         }
         int count3 = AttendanceDetailControl.getCount(3,stuId,ccid+"",stuyear,"","");
         int num3 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(3,stuyear);
         if(num3==0){
       	  flag = false;
         }else{
       	  totalQQ+= (float)count3/num3;
         }
         int count4 = AttendanceDetailControl.getCount(4,stuId,ccid+"",stuyear,"","");
         int num4 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(4,stuyear);
         if(num4==0){
       	  flag = false;
         }else{
       	  totalQQ+= (float)count4/num4;
         }
         int count5 = AttendanceDetailControl.getCount(5,stuId,ccid+"",stuyear,"","");
         int num5 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(5,stuyear);
         if(num5==0){
       	  flag = false;
         }else{
       	  totalQQ+= (float)count5/num5;
         }
         float absenceRate = 0;
         String absencePer="";
         if(countTimes == 0){
         	  //flag = false;
         	   absenceRate = 0;
         	   absencePer = "0";
         } else{
       	  NumberFormat nt = NumberFormat.getPercentInstance();
       	  nt.setMinimumFractionDigits(2);
         	  absenceRate = totalQQ/countTimes;
         	  absencePer =nt.format(absenceRate);
         }
         
         String result="0.0";
         if(flag){
         ParamCourseScoreGS csgs = ParamCourseScoreGSControl.getGSByCourseClassId(ccid);
         result= Double.parseDouble(csgs.getAttendance_per())*(1-absenceRate)+"";
         result = result.length()>3?result.substring(0,4):result;
        // System.out.println(result+"DDDD");
         }
         return result;
		
	}
	 
//	 public static void main(String args[])  {
//		 System.out.println(getAttendanceScoreByCCIDAndStuId(ccid, stuId, stuyear));
//		
//	}
}
